﻿IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_InsertUpdate]
    @Id nvarchar(250),
    @Name nvarchar(250),
    @EndpointId nvarchar(250),
    @Persistent bit,
    @SubscriberDbId bigint,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @MessageValidityDurationSecs bigint = null,
    @DeliveryAttemptIntervalSecs bigint = null,
    @MaxDeliveryAttemptCount int = null,
    @RequestPipelineId nvarchar(250) = null,
    @ResponsePipelineId nvarchar(250) = null,
    @SubscriptionDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE Subscription
        SET
            Name = @Name,
            EndpointId = @EndpointId,
            Persistent = @Persistent,
            DeliveryAttemptIntervalSecs = @DeliveryAttemptIntervalSecs,
            MaxDeliveryAttemptCount = @MaxDeliveryAttemptCount,
            Paused = @Paused,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            MessageValidityDurationSecs = @MessageValidityDurationSecs,
            RequestPipelineId = @RequestPipelineId,
            ResponsePipelineId = @ResponsePipelineId
        WHERE
            Id = @Id AND
            SubscriberDbId = @SubscriberDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @SubscriptionDbId =
            (
                SELECT DbId
                FROM Subscription WITH (NOLOCK)
                WHERE
                    Id = @Id AND
                    SubscriberDbId = @SubscriberDbId
            )

            DELETE FROM Subscription_Filter
            WHERE
                SubscriptionDbId = @SubscriptionDbId
            
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO Subscription
            (
                Id,
                Name,
                EndpointId,
                Persistent,
                DeliveryAttemptIntervalSecs,
                MaxDeliveryAttemptCount,
                SubscriberDbId,
                Paused,
                ExpiryDateTimeUtc,
                MessageValidityDurationSecs,
                RequestPipelineId,
                ResponsePipelineId
            )
            VALUES
            (
                @Id,
                @Name,
                @EndpointId,
                @Persistent,
                @DeliveryAttemptIntervalSecs,
                @MaxDeliveryAttemptCount,
                @SubscriberDbId,
                @Paused,
                @ExpiryDateTimeUtc,
                @MessageValidityDurationSecs,
                @RequestPipelineId,
                @ResponsePipelineId
            )
            SET @SubscriptionDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_Subscription_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_Subscription_Select]
GO
CREATE PROCEDURE [dbo].[sp_Subscription_Select]
    @SubscriberDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            Name,
            EndpointId,
            Persistent,
            Paused,
            ExpiryDateTimeUtc,
            MessageValidityDurationSecs,
            DeliveryAttemptIntervalSecs,
            MaxDeliveryAttemptCount,
            RequestPipelineId,
            ResponsePipelineId
        FROM Subscription WITH (NOLOCK)
        WHERE
            SubscriberDbId = @SubscriberDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_InsertUpdate]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_InsertUpdate]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_InsertUpdate]
    @Id nvarchar(250),
    @Name nvarchar(250),
    @PublisherDbId bigint,
    @EndpointId nvarchar(250) = null,
    @Paused bit,
    @ExpiryDateTimeUtc datetime2 = null,
    @ResponseId nvarchar(250) = null,
    @GenerateDefaultResponse bit = false,
    @RequestPipelineId nvarchar(250) = null,
    @ResponsePipelineId nvarchar(250) = null,
    @ReceivePortDbId bigint = null output
AS
BEGIN
    BEGIN TRY
        UPDATE ReceivePort
        SET
            Name = @Name,
            Paused = @Paused,
            EndpointId = @EndpointId,
            ExpiryDateTimeUtc = @ExpiryDateTimeUtc,
            ResponseId = @ResponseId,
            GenerateDefaultResponse = @GenerateDefaultResponse,
            RequestPipelineId = @RequestPipelineId,
            ResponsePipelineId = @ResponsePipelineId
        WHERE
            Id = @Id AND
            PublisherDbId = @PublisherDbId

        IF @@ROWCOUNT = 1
        BEGIN
            SET @ReceivePortDbId =
            (
                SELECT DbId
                FROM ReceivePort WITH (NOLOCK)
                WHERE
                    Id = @Id AND
                    PublisherDbId = @PublisherDbId
            )
        END

        ELSE IF @@ROWCOUNT = 0
        BEGIN
            INSERT INTO ReceivePort
            (
                Id,
                Name,
                PublisherDbId,
                EndpointId,
                Paused,
                ExpiryDateTimeUtc,
                ResponseId,
                GenerateDefaultResponse,
                RequestPipelineId,
                ResponsePipelineId
            )
            VALUES
            (
                @Id,
                @Name,
                @PublisherDbId,
                @EndpointId,
                @Paused,
                @ExpiryDateTimeUtc,
                @ResponseId,
                @GenerateDefaultResponse,
                @RequestPipelineId,
                @ResponsePipelineId
            )
            SET @ReceivePortDbId = @@IDENTITY
        END

    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[sp_ReceivePort_Select]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[sp_ReceivePort_Select]
GO
CREATE PROCEDURE [dbo].[sp_ReceivePort_Select]
    @PublisherDbId bigint
AS
BEGIN
    BEGIN TRY
        SELECT
            DbId,
            Id,
            Name,
            EndpointId,
            Paused,
            ExpiryDateTimeUtc,
            ResponseId,
            GenerateDefaultResponse,
            RequestPipelineId,
            ResponsePipelineId
        FROM ReceivePort WITH (NOLOCK)
        WHERE
            PublisherDbId = @PublisherDbId AND
            (
                ExpiryDateTimeUtc IS NULL OR
                ExpiryDateTimeUtc > SYSUTCDATETIME()
            )
    END TRY
    BEGIN CATCH
        DECLARE @ErrorMessage NVARCHAR(4000);
        DECLARE @ErrorSeverity INT;
        DECLARE @ErrorState INT;

        SELECT @ErrorMessage = ERROR_MESSAGE(),
               @ErrorSeverity = ERROR_SEVERITY(),
               @ErrorState = ERROR_STATE();

        RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
   END CATCH
END
GO
